﻿using FirebirdSql.Data.FirebirdClient;
using FirebirdSql.Data.Services;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;

namespace Siasun.AFC.Common
{
    /// <summary>
    /// FireBird数据库连接类
    /// </summary>
    public class FirebirdHelper
    {
        /// <summary>
        /// 连接地址
        /// </summary>
        //private string _conn = "172.16.7.99";
        private string _conn = "127.0.0.1";

        //private string _conn = "172.16.1.101";

        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        private string _strConn = string.Empty;

        /// <summary>
        /// 密码
        /// </summary>
        private string _password = "Aa123456";

        /// <summary>
        /// 连接类
        /// </summary>
        private FbConnection _firbird = null;

        /// <summary>
        /// 数据库地址
        /// </summary>
        private string _database = string.Empty;

        /// <summary>
        /// 退出函数
        /// </summary>
        ~FirebirdHelper()
        {
            if (this._firbird != null)
            {
                try
                {
                    this._firbird.Close();
                    this._firbird.Dispose();
                }
                catch (Exception e)
                {
                    LoggerInf.Error("数据库关闭错误");
                }
                this._firbird = null;
            }
        }

        /// <summary>
        /// 当前连接状态
        /// </summary>
        /// <returns>连接状态</returns>
        public bool isConnected()
        {
            if (this._firbird == null)
            {
                return false;
            }
            if (this._firbird.State == ConnectionState.Open)
            {
                return true;
            }
            return false;
        }

        /// <summary>
        /// 传入数据库文件
        /// </summary>
        /// <param name="database">数据库</param>
        public FirebirdHelper(string database)
        {
            this._database = database;
            this.Init();
        }

        /// <summary>
        /// 初始化数据库
        /// </summary>
        /// <param name="database">数据库</param>
        /// <param name="ipAdd">   连接地址</param>
        public FirebirdHelper(string database, string ipAdd)
        {
            this._conn = ipAdd;
            this._database = database;
            this.Init();
        }

        /// <summary>
        /// 析构函数
        /// </summary>
        /// <param name="database">数据库文件</param>
        /// <param name="ipAdd">   地址</param>
        /// <param name="password">密码</param>
        public FirebirdHelper(string database, string ipAdd, string password)
        {
            this._conn = ipAdd;
            this._password = password;
            this._database = database;
            this.Init();
        }

        /// <summary>
        /// 初始化数据库连接
        /// </summary>
        /// <param name="database">数据库</param>
        private void Init()
        {
            try
            {
                FbConnectionStringBuilder config = new FbConnectionStringBuilder();
                config.Database = this._database;
                config.UserID = "SYSDBA";
                config.Password = this._password;
                config.Charset = "UTF8";
                config.ConnectionTimeout = 1500;
                config.DataSource = this._conn;
                config.Pooling = true;
                config.MinPoolSize = 0;
                config.MaxPoolSize = 30;
                config.Port = 3050;
                this._strConn = config.ToString();
                this._firbird = new FbConnection(this._strConn);
            }
            catch (Exception ex)
            {
                this._firbird = null;
                LoggerInf.Warn(ex.ToString());
            }
        }

        /// <summary>
        /// 打开连接
        /// </summary>
        /// <returns></returns>
        public bool Open()
        {
            if (this._firbird == null)
            {
                return false;
            }
            if (this._firbird.State == System.Data.ConnectionState.Open)
            {
                return true;
            }
            try
            {
                this._firbird.Open();
                if (this._firbird.State == System.Data.ConnectionState.Open)
                {
                    return true;
                }
                return false;
            }
            catch (Exception ex)
            {
                LoggerInf.Error(ex.ToString());
            }
            return false;
        }

        /// <summary>
        /// 关闭连接释放资源
        /// </summary>
        public void Close()
        {
            if (this._firbird == null)
            {
                return;
            }
            try
            {
                this._firbird.Close();
                this._firbird.Dispose();
            }
            catch (Exception ex)
            {
                LoggerInf.Error("数据库关闭错误-close");
            }
            this._firbird = null;
        }

        /// <summary>
        /// 检查连接状态
        /// </summary>
        /// <returns></returns>
        private bool DbCheckConnect()
        {
            if (this._firbird == null)
            {
                this.Init();
                this.Open();
            }
            if (!this.isConnected())
            {
                this.Close();
                this.Init();
                this.Open();
                if (!this.isConnected())
                {
                    return false;
                }
            }
            return true;
        }

        /// <summary>
        /// 执行非查询命令
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public bool Execute(string strSql)
        {
            if (!this.DbCheckConnect())
            {
                return false;
            }
            try
            {
                FbCommand cmd = new FbCommand(strSql, this._firbird);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                cmd = null;
                return true;
            }
            catch (Exception ex)
            {
                LoggerInf.Error(strSql + "\n" + ex.ToString());
            }
            return false;
        }

        /// <summary>
        /// 执行非查询命令
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public bool Execute(string strSql, ref int rows)
        {
            if (!this.DbCheckConnect())
            {
                return false;
            }
            try
            {
                FbCommand cmd = new FbCommand(strSql, this._firbird);
                rows = cmd.ExecuteNonQuery();
                cmd.Dispose();
                cmd = null;
                return true;
            }
            catch (Exception ex)
            {
                LoggerInf.Error(strSql + "\n" + ex.ToString());
            }
            return false;
        }

        /// <summary>
        /// 执行读取数据命令
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public List<List<string>> ExecuteRead(string strSql)
        {
            if (!this.DbCheckConnect())
            {
                return null;
            }
            try
            {
                FbCommand cmd = new FbCommand(strSql, this._firbird);
                //cmd.Parameters.Clear();
                FbDataReader reader = cmd.ExecuteReader();

                List<List<string>> listDa = new List<List<string>>();
                while (reader.Read())
                {
                    List<string> buf = new List<string>();
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        buf.Add(reader[i].ToString());
                    }
                    listDa.Add(buf);
                }
                reader.Close();
                reader.Dispose();
                reader = null;
                cmd.Dispose();
                cmd = null;
                return listDa;
            }
            catch (Exception ex)
            {
                LoggerInf.Error(strSql + "\n" + ex.ToString());
            }
            return null;
        }

        /// <summary>
        /// 执行读取数据命令(带有列名)
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns>带有列名的数据</returns>
        public List<Dictionary<string, string>> ExecuteReadDetial(string strSql)
        {
            if (!this.DbCheckConnect())
            {
                return null;
            }
            try
            {
                FbCommand cmd = new FbCommand(strSql, this._firbird);
                //cmd.Parameters.Clear();
                FbDataReader reader = cmd.ExecuteReader();

                List<Dictionary<string, string>> listDa = new List<Dictionary<string, string>>();
                while (reader.Read())
                {
                    Dictionary<string, string> buf = new Dictionary<string, string>();
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        buf.Add(reader.GetName(i).ToUpper().Trim(), reader[i].ToString());
                    }
                    listDa.Add(buf);
                }
                reader.Close();
                reader.Dispose();
                reader = null;
                cmd.Dispose();
                cmd = null;
                return listDa;
            }
            catch (Exception ex)
            {
                LoggerInf.Error(strSql + "\n" + ex.ToString());
            }
            return null;
        }

        /// <summary>
        /// 写入更新二进制带有二进制数据的语句
        /// </summary>
        /// <param name="sqlStr">执行语句</param>
        /// <param name="para">  二进制参数</param>
        /// <returns></returns>
        public bool InsertUpdateWithBlob(string sqlStr, BlobType[] para)
        {
            if (!this.DbCheckConnect())
            {
                return false;
            }
            FbCommand cmdBuf = new FbCommand(sqlStr, this._firbird);
            if (para != null)
            {
                for (int i = 0; i < para.Length; i++)
                {
                    cmdBuf.Parameters.Add(para[i].field, FbDbType.Binary).Value = para[i].fieldData;
                }
            }
            try
            {
                cmdBuf.ExecuteNonQuery();
                cmdBuf.Dispose();
                cmdBuf = null;
                return true;
            }
            catch (Exception ex)
            {
                LoggerInf.Error(sqlStr + "\n" + ex.ToString());
            }
            return false;
        }

        /// <summary>
        /// 执行查询单个blob返回值的语句,输入参数带有blob数据
        /// </summary>
        /// <param name="sqlStr">查询库语句</param>
        /// <param name="para">  二进制参数</param>
        /// <returns></returns>
        public List<List<byte>> SelectWithBlob(string sqlStr, BlobType[] para, int column)
        {
            if (!this.DbCheckConnect())
            {
                return null;
            }
            try
            {
                FbCommand cmdBuf = new FbCommand(sqlStr, this._firbird);
                if (para != null)
                {
                    for (int i = 0; i < para.Length; i++)
                    {
                        cmdBuf.Parameters.Add(para[i].field, FbDbType.Binary).Value = para[i].fieldData;
                    }
                }
                List<List<byte>> rData = new List<List<byte>>();
                IDataReader reader = cmdBuf.ExecuteReader();
                while (reader.Read())
                {
                    for (int i = 0; i < column - 1; i++)
                    {
                        byte[] buf = System.Text.Encoding.UTF8.GetBytes(reader.GetValue(0).ToString());
                        rData.Add(buf.ToList());
                    }
                    byte[] abuf = (byte[])reader.GetValue(1);
                    rData.Add(abuf.ToList());
                }
                reader.Close();
                reader.Dispose();
                reader = null;
                cmdBuf.Dispose();
                cmdBuf = null;
                return rData;
            }
            catch (Exception ex)
            {
                LoggerInf.Error(sqlStr + "\n" + ex.ToString());
            }
            return null;
        }

        /// <summary>
        /// 执行查询单个blob返回值的语句,输入参数带有blob数据
        /// </summary>
        /// <param name="sqlStr">查询库语句</param>
        /// <param name="para">  二进制参数</param>
        /// <returns></returns>
        public List<List<byte>> SelectWithBlob(string sqlStr, BlobType[] para)
        {
            if (!this.DbCheckConnect())
            {
                return null;
            }
            try
            {
                FbCommand cmdBuf = new FbCommand(sqlStr, this._firbird);
                if (para != null)
                {
                    for (int i = 0; i < para.Length; i++)
                    {
                        cmdBuf.Parameters.Add(para[i].field, FbDbType.Binary).Value = para[i].fieldData;
                    }
                }
                List<List<byte>> rData = new List<List<byte>>();
                IDataReader reader = cmdBuf.ExecuteReader();
                while (reader.Read())
                {
                    byte[] abuf = (byte[])reader.GetValue(0);
                    rData.Add(abuf.ToList());
                }
                reader.Close();
                reader.Dispose();
                reader = null;
                cmdBuf.Dispose();
                cmdBuf = null;
                return rData;
            }
            catch (Exception ex)
            {
                LoggerInf.Error(sqlStr + "\n" + ex.ToString());
            }
            return null;
        }

        /// <summary>
        /// 清除全部连接从连接池
        /// </summary>
        public void ClearAllPools()
        {
            FbConnection.ClearAllPools();
        }

        /// <summary>
        /// 清除本次连接从连接池
        /// </summary>
        public void ClearPool()
        {
            if (this._firbird != null)
            {
                FbConnection.ClearPool(this._firbird);
            }
        }

        /// <summary>
        /// 数据库还原
        /// </summary>
        /// <param name="pathBak">文件路径</param>
        public static void RestoreDb(string dataBasebakPath, string databasePathSource,
            string password, string user, string ipAddr)
        {
            try
            {
                FbConnectionStringBuilder config = new FbConnectionStringBuilder();
                config.Database = databasePathSource;
                config.UserID = "SYSDBA";
                config.Password = password;
                config.Charset = "UTF8";
                config.ConnectionTimeout = 1500;
                config.DataSource = ipAddr;
                config.Pooling = true;
                config.MinPoolSize = 0;
                config.MaxPoolSize = 30;
                config.Port = 3050;
                string strConn = config.ToString();

                FbRestore res = new FbRestore();
                res.ConnectionString = strConn;
                res.BackupFiles.Add(new FbBackupFile(dataBasebakPath, 2048));
                res.Verbose = true;
                res.PageSize = 4096;
                res.Options = FbRestoreFlags.Create | FbRestoreFlags.Replace;
                res.Execute();
            }
            catch (Exception ex)
            {
                LoggerInf.Error("数据库还原" + "\n" + ex.ToString());
            }
        }

        /// <summary>
        /// 数据库还原
        /// </summary>
        /// <param name="dataBasebakPath">   备份文件地址</param>
        /// <param name="databasePathSource">还原后地址</param>
        public static void RestoreDb(string dataBasebakPath, string databasePathSource)
        {
            RestoreDb(dataBasebakPath, databasePathSource, "Aa123456", "SYSDBA", "127.0.0.1");
        }

        /// <summary>
        /// 数据库备份
        /// </summary>
        /// <param name="pathBak">文件路径</param>
        public static void BackUpDb(string dataBasebakPath, string databasePathSource,
            string password, string user, string ipAddr)
        {
            try
            {
                FbConnectionStringBuilder config = new FbConnectionStringBuilder();
                config.Database = databasePathSource;
                config.UserID = "SYSDBA";
                config.Password = password;
                config.Charset = "UTF8";
                config.ConnectionTimeout = 1500;
                config.DataSource = ipAddr;
                config.Pooling = false;
                //config.MinPoolSize = 0;
                //config.MaxPoolSize = 30;
                config.Port = 3050;
                string strConn = config.ToString();

                FbBackup bakup = new FbBackup();
                bakup.ConnectionString = strConn;
                bakup.BackupFiles.Add(new FbBackupFile(dataBasebakPath, 2048));
                bakup.Verbose = true;
                bakup.Options = FbBackupFlags.IgnoreLimbo;
                bakup.Execute();
            }
            catch (Exception ex)
            {
                LoggerInf.Error("数据库备份" + "\n" + ex.ToString());
            }
        }

        /// <summary>
        /// 备份数据库
        /// </summary>
        /// <param name="dataBasebakPath">   备份文件地址</param>
        /// <param name="databasePathSource">数据库原地址</param>
        public static void BackUpDb(string dataBasebakPath, string databasePathSource)
        {
            BackUpDb(dataBasebakPath, databasePathSource, "Aa123456", "SYSDBA", "127.0.0.1");
        }

        /// <summary>
        /// 检查数据库是否损坏
        /// </summary>
        /// <param name="dbPath">数据库地址</param>
        /// <returns>是否损坏</returns>
        public static bool CheckDbStates(string dbPath)
        {
            try
            {
                FbConnectionStringBuilder config = new FbConnectionStringBuilder();
                config.Database = dbPath;
                config.UserID = "SYSDBA";
                config.Password = "Aa123456";
                config.Charset = "UTF8";
                config.ConnectionTimeout = 1500;
                config.DataSource = "127.0.0.1";
                config.Pooling = false;
                config.Port = 3050;
                FbConnection fBuf = new FbConnection(config.ToString());
                fBuf.Open();
                bool isConnect = (fBuf.State == ConnectionState.Connecting) ||
                    (fBuf.State == ConnectionState.Executing) ||
                    (fBuf.State == ConnectionState.Fetching) ||
                    (fBuf.State == ConnectionState.Open);
                fBuf.Close();
                fBuf.Dispose();
                return isConnect;
            }
            catch (Exception ex)
            {
                LoggerInf.Warn(ex.ToString());
            }
            return false;
        }

        /// <summary>
        /// 二进制类型
        /// </summary>
        public class BlobType
        {
            /// <summary>
            /// blob字段
            /// </summary>
            public string field = string.Empty;

            /// <summary>
            /// 字段值
            /// </summary>
            public byte[] fieldData = null;
        }
    }
}